PostgreSQL has more than 35 years of active development under its belt making it one of the most powerful and reliable relational database management systems (RDBMS). Even today it’s the fourth most popular database in the world, backed by a global community of dedicated supporters.
It’s a good fit for business critical workloads because PostgreSQL delivers a highly stable foundation and is ACID compliant. This is also why it’s often used in banking and for use cases that handle payments.
In this post, we are going to look at when PostgreSQL may no longer be suited for your banking applications and business needs.
Horizontal scale for transactions per second
One of the well known limitations of PostgreSQL is its lack of horizontal scalability: Postgres is built to scale up, not out. If you are building an application such as a payment system that will be processing hundreds of thousands of transactions per second, you should prepare for scale. You don’t want your development team to have to manually shard the database to help it achieve scale.
Conceptually, simply sharding PostgreSQL may look like the logical path. However, everywhere you set up a new database instance will require ongoing maintenance such as changing your schema or taking the database offline for upgrades. And, in order to achieve a fault-tolerant system, you will also need to set up replication.
A sharded system limits your development speed which creates additional costs, wastes developer hours, and introduces risk to the business.
Companies that truly understand the overhead and opportunity costs of manual sharding — both fintechs and large multinational banks — have given up on this slow and painstaking methodology. Instead, they have switched to distributed SQL databases, which offer built-in horizontal scalability and replication.
Distributed architecture for truly distributed transactions
You either have a global customer base now, or you are working to get one. As discussed above, setting up and managing disparate PostgreSQL instances in several locations comes with a lot of overhead. Plus, you aren’t able to control data residency — an important factor for finance related applications or systems, which are subject to a variety of financial service compliance regulations.
Some organizations try to overcome the management challenges of distributed database instances by using a managed PostgreSQL solution. Managed PostgreSQL is provided by all the major cloud providers – AWS RDS for PostgreSQL, Google Cloud SQL for PostgreSQL or Azure Database for PostgreSQL – among others.
However, all of these systems can be considered “half-distributed” in the sense that they’re able to distribute reads across machines in a cluster, but depend on a single write node which creates a write bottleneck. This is not an ideal solution for a system that needs to process distributed transactions (i.e. payments, account balances) that are constantly arriving in near-real time.
By contrast, a distributed SQL database like CockroachDB is “fully distributed” which eliminates the write bottleneck when scaling. CockroachDB can scale-out both writes and reads across low-cost machines resulting in better price-performance, improved resiliency, and lower latency.
Schedule downtime and unplanned outages
There are a number of events that can cause unplanned downtime. Catastrophic storms impacting data centers, large influx of unexpected traffic to your app, cloud provider system outages, and so on. Even though PostgreSQL is a resilient system, it was not designed to handle black swan events.
In these situations, even though your data might be replicated and there’s a failover procedure in place, an outage can impact data consistency and you can even lose data. With PostgreSQL, transactional consistency is only guaranteed in a single instance, not across a distributed environment.
Additionally, planned downtime, which is often required for PostgreSQL updates and upgrades, can be just as costly in the long run. Even if you only have to take the system down once or twice a year, every minute counts and can impact your bottom line. Plus, no amount of downtime is acceptable for systems that handle people’s money.
Why Stash migrated from PostgreSQL to CockroachDB
When Stash engineers started designing their new banking infrastructure, they built an initial design on PostgreSQL. A lot of the engineers were already familiar with PostgreSQL and knew that it delivered data consistency and a resilient foundation, which were both crucial capabilities for their system that would handle money.
[ case study ]
Full sp(r)eed ahead: an operationally efficient payment infrastructure
read case study →However, a mission-critical banking platform has two other important requirements: 1) achieving five-nines of availability and 2) building on a multi-region, multi-cloud foundation. They never wanted their customers to worry about incorrect balances or run into latency issues when spending money. Looking to the future, it also made sense for Stash to build a platform that could easily scale to multiple regions and replicate data across those regions for a stronger fault-tolerant setup.
While these requirements are technically possible with PostgreSQL, the Stash team knew it would be complicated to set up and even harder to maintain. One of the Principal Engineers on the project suggested taking a look at CockroachDB, a cloud-native distributed database that met their requirements. Fast forward to today, where CockroachDB serves as a system of record (or source of truth) for their entire banking infrastructure.
[Read the full story on why Stash choose CockroachDB]
In summary
We’ve seen dozens of companies make the switch from PostgreSQL to CockroachDB. Whether they are like Stash and want a solution that can grow with them in the future, like Shipt that wanted to build an idempotent payment system designed for correctness, or like Stake who couldn’t suffer another outage due to a trading frenzy.
PostgreSQL and CockroachDB are compatible and our customers report that making the switch is relatively straightforward. No hours of refactoring code, no learning a new query language. If you are interested in migrating off of PostgreSQL to CockroachDB, get in touch.
